﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace PreluareDateDePeEBay
{
    public class Database1
    {
        private SqlConnection conn = new SqlConnection("Data Source=mail.pioneerinvestments.ro\\sql2008,1333;Initial Catalog=Cotrobaie;Persist Security Info=True;User ID=sa;Password=sa1234");


        // Functie pentru inserarea noilor produse in tabela Product
        public int insertProduct(String name, String brand, String description, String imageSwatch, String imageSmall, String imageMedium, String imageLarge, String UPC, String ISBN, int categ_id, String authors, String priceEbay, String linkEbay)
        {
            int err_code = 0;
            int id = -1;
            int nrExemplare = 0;

            SqlDataReader reader;
            SqlCommand cmdReader;

            //Daca e carte va avea cel mai probabil ISBN
            if (ISBN != "")
            {
                cmdReader = new SqlCommand("SELECT id FROM PRODUCT WHERE ISBN = @isbn", conn);
                cmdReader.CommandType = System.Data.CommandType.Text;

                cmdReader.Parameters.Add("isbn", System.Data.SqlDbType.VarChar).Value = ISBN;

                try
                {
                    conn.Open();
                    reader = cmdReader.ExecuteReader();
                }
                catch (Exception e)
                {
                    return -1;
                }
            }
            // altfel va avea UPC
            else
            {
                cmdReader = new SqlCommand("SELECT id FROM PRODUCT WHERE upc = @upc", conn);
                cmdReader.CommandType = System.Data.CommandType.Text;

                cmdReader.Parameters.Add("upc", System.Data.SqlDbType.VarChar).Value = UPC;

                try
                {
                    conn.Open();
                    reader = cmdReader.ExecuteReader();
                }
                catch (Exception e)
                {
                    return -1;
                }
            }

            while (reader.Read())
            {
                nrExemplare++;
                id = int.Parse(reader["id"].ToString());
            }

            reader.Dispose();

            // Daca numarul de exemplare este 0, inseamna ca produsul nu a mai fost adaugat, asa ca il voi adauga.*
            if (nrExemplare == 0)
            {
                // Daca produsul nu exista in tabela Produs atunci il introduc
                SqlCommand cmd = new SqlCommand("INSERT INTO Product VALUES(@name,@brand,@description,@imageSwatch,@imageSmall,@imageMedium,@imageLarge,@UPC,@ISBN,@categ_id,@authors,'',@linkEbay,@currPrice,'')", conn);
                cmd.CommandType = System.Data.CommandType.Text;

                cmd.Parameters.Add("name", System.Data.SqlDbType.VarChar).Value = name;
                cmd.Parameters.Add("brand", System.Data.SqlDbType.VarChar).Value = brand;
                cmd.Parameters.Add("description", System.Data.SqlDbType.Text).Value = description;
                cmd.Parameters.Add("imageSwatch", System.Data.SqlDbType.VarChar).Value = imageSwatch;
                cmd.Parameters.Add("imageSmall", System.Data.SqlDbType.VarChar).Value = imageSmall;
                cmd.Parameters.Add("imageMedium", System.Data.SqlDbType.VarChar).Value = imageMedium;
                cmd.Parameters.Add("imageLarge", System.Data.SqlDbType.VarChar).Value = imageLarge;
                cmd.Parameters.Add("UPC", System.Data.SqlDbType.VarChar).Value = UPC;
                cmd.Parameters.Add("ISBN", System.Data.SqlDbType.VarChar).Value = ISBN;
                cmd.Parameters.Add("categ_id", System.Data.SqlDbType.Int).Value = categ_id;
                cmd.Parameters.Add("authors", System.Data.SqlDbType.VarChar).Value = authors;
                cmd.Parameters.Add("linkEbay", System.Data.SqlDbType.VarChar).Value = linkEbay;
                cmd.Parameters.Add("currPrice", System.Data.SqlDbType.Float).Value = priceEbay;

                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    err_code = -1;
                    return err_code;
                }

                cmd.Dispose();
            }
            else
            {
                // Daca produsul exista in tabela produs, atunci updatez aceasta tabela cu linkEbay-ul curent
                SqlCommand cmd = new SqlCommand("UPDATE Product SET linkEbay = @linkEbay where id = @id ", conn);
                cmd.CommandType = System.Data.CommandType.Text;

                cmd.Parameters.Add("id", System.Data.SqlDbType.BigInt).Value = id;
                cmd.Parameters.Add("linkEbay", System.Data.SqlDbType.VarChar).Value = linkEbay;

                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    err_code = -1;
                    return err_code;
                }

                cmd.Dispose();
            }

            conn.Close();

            return err_code;
        }
      }
    }

